<?php
/**
 * 统计的公共类
 * User: crstu
 * Date: 2015/6/15
 * Time: 18:16
 */

namespace common\models\Service;

use Yii;
use yii\helpers\ArrayHelper;
use backend\models\DAO\UserInvest;
use backend\models\DAO\UserProceeds;
use backend\models\DAO\DayAccrual;
use backend\models\DAO\ProductList;


class StatsService {

    /*
     * 操作类型声明：
     * $item = 's'|'c'|'sc'
     * [
     *     s: sum
     *     c: count
     *     sc: sum 和 count；顺序是先sum，后count
     * ]
     */


    /**
     * 根据条件统计sum
     * @param string $type 操作类型
     * @param string $table 表名，不带前缀，下划线
     * @param string $field 字段名
     * @param array|string $condition 数组形式范例：[['>', createTime, $time], ['=', $id, 12], ...]
     * @return string|int|array
     */
    public static function sumAndCountByField($item, $table, $field, $condition = array())
    {
        $item = strtolower($item);
        if($item == 's'){
            $sql = 'SELECT SUM([['.$field.']]) FROM {{%'.$table.'}}';
        }elseif($item == 'c'){
            $sql = 'SELECT COUNT([['.$field.']]) FROM {{%'.$table.'}}';
        }elseif($item == 'sc'){
            $sql = 'SELECT SUM([['.$field.']]),COUNT([['.$field.']]) FROM {{%'.$table.'}}';
        }else{
            return 'ITEM ERROR';
        }

        $db = Yii::$app->db;
        if(!empty($condition)){
            $sql .= ' WHERE ';
            if(is_array($condition)){
                $where = array();
                $value = array();
                foreach($condition as $k => $group){
                    $where[] = $group[1] . $group[0] . ':value_' . $k;
                    $value[':value_'.$k] = $group[2];
                }
                $sql .= implode(' AND ', $where);
                $res = $db->createCommand($sql)->bindValues($value)->queryOne();

            }else{
                $sql .= $condition;
                $res = $db->createCommand($sql)->queryOne();
            }
        }else{
            $res = $db->createCommand($sql)->queryOne();
        }

        if($item == 's' || $item == 'c'){
            return intval(reset($res));
        }else{
            return [
                0 => intval(reset($res)),
                'sum' => intval(current($res)),
                1 => intval(next($res)),
                'count' => intval(current($res)),
            ];
        }
    }

    /**
     * 统计某个表中的某个字段从开始时间到结束时间的数量
     * @param $item
     * @param $table
     * @param $field
     * @param $start_time
     * @param $end_time
     * @param string $time_field
     * @param array $condition
     * @return array|int|string
     */
    public static function sumAndCountByFieldByTime($item, $table, $field, $start_time, $end_time, $time_field = 'createTime', $condition = [])
    {
        if(!empty($end_time)){
            $condition[] = ['<', $time_field, $end_time];
        }
        if(!empty($start_time)){
            $condition[] = ['>', $time_field, $start_time];
        }

        return self::sumAndCountByField($item, $table, $field, $condition);
    }

    /*
     * 分组计算某个表的某个字段百分比
     * @param $table
     * @param $field
     * @param array $condition
     * @return array|bool
     */
    public static function countGroupByField($table, $field, $condition = array())
    {
        $sql = 'SELECT `'.$field.'`,COUNT('.$field.') FROM {{%'.$table.'}} ';

        $db = Yii::$app->db;
        if(!empty($condition)){
            $sql .= ' WHERE ';
            if(is_array($condition)){
                $where = array();
                $value = array();
                foreach($condition as $k => $group){
                    $where[] = $group[1] . $group[0] . ':value_' . $k;
                    $value[':value_'.$k] = $group[2];
                }
                $sql .= implode(' AND ', $where);
                $sql .= ' GROUP BY [['.$field.']]';
                $res = $db->createCommand($sql)->bindValues($value)->queryAll();

            }else{
                $sql .= $condition;
                $sql .= 'GROUP BY [['.$field.']]';
                $res = $db->createCommand($sql)->queryAll();
            }
        }else{
            $sql .= 'GROUP BY [['.$field.']]';
            $res = $db->createCommand($sql)->queryAll();
        }

        $data = [];
        foreach($res as $k => $v){
            $data[$v[$field]] = $v['COUNT('.$field.')'];
        }
        return $data;
    }

    /**
     * 根据提供时间字段，将一天分割成24个小时，统计各个时间的总数量，返回一个有24个键值的数组
     * @param string $table 表名
     * @param string $field 字段名
     * @param int $star_time
     * @param int $end_time
     * @param string string $time_field
     * @return array
     */
    public static function countFieldByEveryHour($table, $start_time, $end_time, $time_field = 'createTime'){
        $where = array();
        if(!empty($end_time)){
            $where[] = $time_field.'<'.$end_time;
        }
        if(!empty($start_time)){
            $where[] = $time_field.'>'.$start_time;
        }
        $return = [];
        $db = Yii::$app->db;
        for($i=0; $i<24; $i++){
            $j = $i+1;
            $where[] = 'HOUR(FROM_UNIXTIME('.$time_field.'))>'.$i;
            $where[] = 'HOUR(FROM_UNIXTIME('.$time_field.'))>'.$j;
            $where_sql = implode(' AND ', $where);
            $sql = "SELECT COUNT(id) FROM {{%$table}} WHERE ".$where_sql;
            $res = $db->createCommand($sql)->queryOne();
            $index = $i.'点-'.$j.'点';
            $return[$index] = intval($res['COUNT(id)']);
        }
        return $return;
    }

    /**
     * 分组统计某个字段的值从开始时间到结束时间
     * @param $table
     * @param $field
     * @param $start_time
     * @param $end_time
     * @param string $time_field
     * @param array $condition
     * @return array
     */
    public static function countGroupByFieldByTime($table, $field, $start_time, $end_time, $time_field = 'createTime', $condition = [])
    {
        $condition = [];
        if(!empty($end_time)){
            $condition[] = ['<', $time_field, $end_time];
        }
        if(!empty($start_time)){
            $condition[] = ['>', $time_field, $start_time];
        }

        $result = self::countGroupByField($table, $field, $condition);

        return $result;
    }


    /*
     * 根据时间段统计这段时间内的项目
     * @param string $item
     * @param string $table
     * @param string $field
     * @param int $start_time 开始时间
     * @param int $end_time 结束时间
     * @return int 只返回一个数字
     */
    public static function statsByTimePeriod($item, $table, $field, $start_time = 0, $end_time = 0, $time_field = 'createTime'){
        $condition = array();
        if(!empty($start_time)){
            $condition[] = ['>', $time_field, $start_time];
        }
        if(!empty($end_time)){
            $condition[] = ['<', $time_field, $end_time];
        }
        return self::sumAndCountByField($item, $table, $field, $condition);
    }


    /**
     * 根据传入的时间数组来统计并返回值
     * @param string $item
     * @param string $table
     * @param string $field
     * @param array $time_array 时间数组
     * @param string $time_field 时间字段
     * @param array $condition 二维数组，条件
     * @return array 返回的是一个一维数组
     */
    public static function statsByTimeArray($item, $table, $field, $time_array, $time_field = 'createTime', $condition = []){
        $data = [];
        if(isset($time_array)){
            $length = count($time_array);
            foreach($time_array as $k => $v){
                if(($k < $length-1)){
                    $condition_new = $condition;
                    $condition_new[] = ['>', $time_field, $v];
                    $condition_new[] = ['<', $time_field, $time_array[$k+1]];
                    $data[] = self::sumAndCountByField($item, $table, $field, $condition_new);
                }
            }
        }
        return $data;
    }


    /**
     * 根据起始日期和结束日期统计某个表的某个字段（这个方法和statsByTimePeriod的区别是这个将一个时间段解析成天，返回的是数组）
     * @param string|int $item 统计项目（s:sum; c:count）
     * @param string $table 表名
     * @param string $field 字段
     * @param string $start_date 开始日期时间戳
     * @param string $end_date 结束日期时间戳
     * @param string $time_field 比较的时间字段
     * @return array
     */
    public static function statsByDay($item, $table, $field, $start_time, $end_time, $time_field = 'createTime', $condition = [])
    {
        $days = TimeService::initSomeDays($start_time, $end_time);
        $output = array();
        foreach($days['data'] as $k => $v){
            $result = self::sumAndCountByFieldByTime($item, $table, $field, $v, $v+3600*24, $time_field);
            if(is_array($result)){
                if(isset($result['sum']))
                    $output['sum'][]   = $result['sum'];
                if(isset($result['count']))
                    $output['count'][] = $result['count'];
            }else{
                $output[] = $result;
            }
        }

        return $output;
    }




    /*
     * 分组统计
     */
    public static function statsGroupByDay($table, $field, $start_time, $end_time = 0, $time_field = 'createTime', $condition = [])
    {
        $output = [];

        if(is_array($start_time)){
            foreach($start_time as $k => $v){
                $result = self::countGroupByFieldByTime($table, $field, $v, $v+3600*24, $time_field);
                if(is_array($result)){
                    if(isset($result['sum']))
                        $output['sum'][]   = $result['sum'];
                    if(isset($result['count']))
                        $output['count'][] = $result['count'];
                }else{
                    $output[] = $result;
                }
            }
        }else{
            $days = TimeService::initSomeDays($start_time, $end_time);
            $output = array();
            foreach($days['data'] as $k => $v){
                $result = self::countGroupByFieldByTime($table, $field, $v, $v+3600*24, $time_field);
                if(is_array($result)){
                    if(isset($result['sum']))
                        $output['sum'][]   = $result['sum'];
                    if(isset($result['count']))
                        $output['count'][] = $result['count'];
                }else{
                    $output[] = $result;
                }
            }
        }

        return $output;
    }


    /*
     * 按天统计某月某个表的某个字段的总和或者次数
     * @param string $item 统计项目
     * @param string $table
     * @param string $field
     * @param string $date
     * @param string $time_field
     */
    public static function statOneMonthByDay($item, $table, $field, $date = null, $time_field = 'createTime')
    {
        $TimeService = new TimeService();
        $one_month = $TimeService->initOneMonthByDay($date);

        $output = array();
        foreach($one_month as $k => $v){
            $result = self::sumAndCountByField($item, $table, $field, [['>', $time_field, $v], ['<', $time_field, ($v+3600*24)]]);
            if(is_array($result)){
                $output['sum'][]   = $result['sum'];
                $output['count'][] = $result['count'];
            }else{
                $output[] = $result;
            }
        }

        return $output;
    }






//    public static function sumModelByFieldSomeDay($table, $field, $days = 10){
//        $db = Yii::$app->db;
//        $TimeService = new TimeService();
//        $title = '近'.$days.'天的统计情况';
//        $stat_some_days = $TimeService->initStatSomeDays($days, $title);
//        $last_some_days = $TimeService->getLastSomeDays($days, $title);
//        foreach($last_some_days as $k => $d){
//            if(isset($last_some_days[$k+1])){
//                $db->createCommand('SELECT * FROM {{$table}} WHERE createTime>:start AND createTime<:end');
//                $stat_some_days['data']['近10天的统计情况'][$k] = $Model->sumByTime($field, $d, $last_some_days[$k+1]);
//            }
//        }
//
//        return $stat_some_days;
//    }


	/**
	 * 未来N天 预计提现的金额 (定期)
	 */
	public static function getEndInvest($days = 3)
	{
		for($i=1; $i<=$days; $i++)
		{
			$date = date('Ymd',strtotime('+'.$i.' days'));
			$data[$date] = UserInvest::find()->where(['endTime'=>$date])->select('sum(money+accrual) as sum')->asArray()->all()[0]['sum']+0;
		}
		return $data;
	}
	
	/**
	 * 某款产品的总实时金额，总笔数
	 * @param int $product_id 产品id
	 */
	public static function getProductDataRealTime($product_id = 2)
	{
		$date=date('Ymd');
		return UserInvest::find()->where(['productID'=>$product_id])->andWhere("endTime > $date")->select('sum(money) as money,count(*) as total')->asArray()->one();
	}
	
	/**
	 * 某段时间内某款产品的总金额，总笔数，大于1000元的笔数
	 * $product_id 产品id $start $end 时间戳
	 */
	public static function getProductDataInWindow($product_id=2,$start,$end)
	{
		$connection = \Yii::$app->db; 
		$data=$connection->createCommand("SELECT count(a.id) as total,sum(a.money) as money,count(b.id) as bigdeal 
										  FROM p2p_user_invest as a 
										  left join p2p_user_invest as b 
										  on a.id=b.id and b.money>100000 
										  where a.productID=$product_id and a.createTime >= $start and a.createTime < $end")->queryAll()[0];
		return $data;
	}
	
	/**
	 * 某段时间内某款产品的每天的当天金额，当天笔数
	 * $product_id 产品id $start $end 时间戳
	 */
	public static function getProductDataInDays($product_id=2,$start,$end)
	{
		$data=UserInvest::find()->where(['productID'=>$product_id])->andWhere("createTime >= $start and createTime < $end")->select('sum(money) as money,count(*) as total,createTime')->groupBy('endTime')->asArray()->all();
		foreach($data as $v)
		{
			$return[date('Ymd',$v['createTime'])] = $v;
		}
		return $return;
	}





}